﻿Public Class ESE

    Declare Ansi Function JetGetDatabaseFileInfo Lib "esent.dll" (ByVal szDatabaseName As String, ByRef pvResult As Integer, ByVal cbMax As Integer, ByVal InfoLevel As Integer) As Integer
    Declare Ansi Function JetGetDatabaseFileInfo Lib "esent.dll" (ByVal szDatabaseName As String, ByRef pvResult As JET_DBINFOMISC, ByVal cbMax As Integer, ByVal InfoLevel As Integer) As Integer
    Declare Ansi Function JetCreateInstance Lib "esent.dll" (ByRef instance As IntPtr, ByVal szInstanceName As String) As Integer
    Declare Function JetInit Lib "esent.dll" (ByRef instance As IntPtr) As Integer
    Declare Ansi Function JetBeginSession Lib "esent.dll" (ByVal instance As IntPtr, ByRef psesid As IntPtr, ByVal szUserName As String, ByVal szPassword As String) As Integer
    Declare Ansi Function JetAttachDatabase Lib "esent.dll" (ByVal sesid As IntPtr, ByVal szFilename As String, ByVal grpit As Integer) As Integer
    Declare Ansi Function JetOpenDatabase Lib "esent.dll" (ByVal sesid As IntPtr, ByVal szFilename As String, ByVal szConnect As String, ByRef pdbid As Integer, ByVal grpit As Integer) As Integer
    Declare Ansi Function JetOpenTable Lib "esent.dll" (ByVal sesid As IntPtr, ByVal dbid As Integer, ByVal szTableName As String, ByVal pvParameters As Integer, ByVal cbParameters As Integer, ByVal grbit As Integer, ByRef ptableid As IntPtr) As Integer
    Declare Ansi Function JetGetTableColumnInfo Lib "esent.dll" (ByVal sesid As IntPtr, ByVal tableid As IntPtr, ByVal szColumnName As String, ByVal pvResult As IntPtr, ByVal cbMax As Integer, ByVal InfoLevel As Integer) As Integer
    Declare Function JetRetrieveColumn Lib "esent.dll" (ByVal sesid As IntPtr, ByVal tableid As IntPtr, ByVal columnid As Integer, ByVal pvData As IntPtr, ByVal cbData As Integer, ByRef pcbActual As Integer, ByVal grbit As Integer, ByRef pretinfo As JET_RETINFO) As Integer
    Declare Function JetMove Lib "esent.dll" (ByVal sesid As IntPtr, ByVal tableid As IntPtr, ByVal cRow As Integer, ByVal grbit As Integer) As Integer
    Declare Function JetCloseTable Lib "esent.dll" (ByVal sesid As IntPtr, ByVal tableid As IntPtr) As Integer
    Declare Function JetCloseDatabase Lib "esent.dll" (ByVal sesid As IntPtr, ByVal dbid As Integer, ByVal grpit As Integer) As Integer
    Declare Ansi Function JetDetachDatabase Lib "esent.dll" (ByVal sesid As IntPtr, ByVal szFilename As String) As Integer
    Declare Function JetEndSession Lib "esent.dll" (ByVal sesid As IntPtr, ByVal grbit As Integer) As Integer
    Declare Function JetTerm Lib "esent.dll" (ByVal instance As IntPtr) As Integer
    Declare Ansi Function JetGetSystemParameter Lib "esent.dll" (ByVal instance As IntPtr, ByVal sesid As IntPtr, ByVal paramid As Integer, ByRef plParam As Integer, ByVal szParam As String, ByVal cbMax As Integer) As Integer
    Declare Ansi Function JetSetSystemParameter Lib "esent.dll" (ByRef pinstance As IntPtr, ByVal sesid As IntPtr, ByVal paramid As Integer, ByVal lParam As Integer, ByVal szParam As String) As Integer
    Declare Ansi Function JetBeginTransaction Lib "esent.dll" (sesid As IntPtr) As Integer
    Declare Ansi Function JetPrepareUpdate Lib "esent.dll" (sesid As IntPtr, tableid As IntPtr, prep As Integer) As Integer
    Declare Ansi Function JetSetColumn Lib "esent.dll" (sesid As IntPtr, tableid As IntPtr, columnid As Integer, bytes() As Byte, cbData As Integer, grbit As Integer, psetinfo As IntPtr) As Integer
    Declare Ansi Function JetUpdate Lib "esent.dll" (sesid As IntPtr, tableid As IntPtr, ByRef pvBookmark() As Byte, cbBookmark As Integer, ByRef cbActual As Integer) As Integer
    Declare Ansi Function JetCommitTransaction Lib "esent.dll" (sesid As IntPtr, grbit As Integer) As Integer

    Enum DatabaseFlags
        OpenDefault = 0
        OpenDbReadOnly = 1
        OpenDbExclusive = 2
        OpenTableReadOnly = 4
    End Enum

    Const JET_ColInfoListSortColumnid As Integer = 7

    Const JET_paramErrorToString As Integer = 70
    Const JET_paramTempPath As Integer = 1
    Const JET_paramLogFilePath As Integer = 2
    Const JET_paramSystemPath As Integer = 0
    Const JET_paramAccessDeniedRetryPeriod As Integer = 53
    Const JET_paramDatabasePageSize As Integer = 64
    Const JET_paramCircularLogging As Integer = 17

    Const JET_cbNameMost As Integer = 64
    Const JET_wrnColumnNull As Integer = 1004
    Const JET_wrnBufferTruncated As Integer = 1006

    Const JET_MoveFirst As Integer = &H80000000
    Const JET_MoveNext As Integer = 1

    Enum ColumnTypes
        [Null] = 0
        [Boolean] = 1
        [UInt8] = 2
        [Short] = 3
        [Integer] = 4
        [Currency] = 5
        [Single] = 6
        [Double] = 7
        [DateTime] = 8
        [Binary] = 9
        [Text] = 10
        [LongBinary] = 11
        [LongText] = 12
        [SLV] = 13
        [UInt32] = 14
        [Long] = 15
        [GUID] = 16
        [UInt16] = 17
        [Max] = 18
    End Enum

    Const JET_DbInfoPageSize As Integer = 17

    Const JET_PrepInsert As Integer = 0
    Const JET_PrepReplace As Integer = 2

    Const BUF_SIZE As Integer = 8192

    Private Structure ESEDatabase
        Public Path As String
        Public InstanceID As IntPtr
        Public SessionID As IntPtr
        Public DatabaseID As Integer
    End Structure

    Shared Function GetTable(ByVal databaseFile As String, ByVal tableName As String) As DataTable
        Dim db As ESEDatabase
        Dim dt As DataTable
        Dim col As DataColumn
        Dim dr As DataRow
        Dim iPtr As IntPtr
        Dim tableID As IntPtr
        Dim ret As Integer
        Dim size As Integer
        Dim columnList As JET_COLUMNLIST
        Dim ret_info As JET_RETINFO
        Dim columnName As String
        Dim columnID As Integer
        Dim columnType As ColumnTypes

        db = OpenDatabase(databaseFile)

        ' open the table
        ret = JetOpenTable(db.SessionID, db.DatabaseID, tableName, 0, 0, DatabaseFlags.OpenTableReadOnly, tableID)
        If ret <> 0 Then
            Throw New ApplicationException("JetOpenTable: " & ParseError(ret))
        End If

        columnList = Nothing
        iPtr = Marshal.AllocHGlobal(Marshal.SizeOf(columnList))
        ret = JetGetTableColumnInfo(db.SessionID, tableID, Nothing, iPtr, Marshal.SizeOf(columnList), JET_ColInfoListSortColumnid)
        If ret <> 0 Then
            Throw New ApplicationException("JetGetTableColumnInfo: " & ParseError(ret))
        End If

        columnList = CType(Marshal.PtrToStructure(iPtr, GetType(JET_COLUMNLIST)), JET_COLUMNLIST)

        Marshal.FreeHGlobal(iPtr)

        ret = JetMove(db.SessionID, columnList.tableid, JET_MoveFirst, 0)
        If ret <> 0 Then
            Throw New ApplicationException("JetMove: " & ParseError(ret))
        End If

        iPtr = Marshal.AllocHGlobal(JET_cbNameMost)
        ret_info.cbStruct = Marshal.SizeOf(ret_info)
        ret_info.itagSequence = 1

        ' Loop thorugh records
        dt = New DataTable
        dt.TableName = tableName
        dt.ExtendedProperties.Add("DatabaseFile", databaseFile)

        Do While ret = 0
            ' column name
            ret = JetRetrieveColumn(db.SessionID, columnList.tableid, columnList.columnidcolumnname, iPtr, JET_cbNameMost, size, 0, ret_info)
            If ret <> 0 Then
                Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
            End If
            columnName = Marshal.PtrToStringAnsi(iPtr, size)

            ' column data type
            ret = JetRetrieveColumn(db.SessionID, columnList.tableid, columnList.columnidcoltyp, iPtr, 4, size, 0, ret_info)
            If ret <> 0 Then
                Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
            End If
            columnType = CType(Marshal.ReadInt32(iPtr), ColumnTypes)

            ' column ID
            ret = JetRetrieveColumn(db.SessionID, columnList.tableid, columnList.columnidcolumnid, iPtr, 4, size, 0, ret_info)
            If ret <> 0 Then
                Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
            End If
            columnID = Marshal.ReadInt32(iPtr)

            Select Case columnType
                Case ColumnTypes.Boolean
                    col = New DataColumn(columnName, GetType(System.Boolean))

                Case ColumnTypes.UInt8
                    col = New DataColumn(columnName, GetType(System.Byte))

                Case ColumnTypes.Short
                    col = New DataColumn(columnName, GetType(System.Int16))

                Case ColumnTypes.Integer
                    col = New DataColumn(columnName, GetType(System.Int32))

                Case ColumnTypes.Currency
                    col = New DataColumn(columnName, GetType(System.Decimal))

                Case ColumnTypes.Single
                    col = New DataColumn(columnName, GetType(System.Single))

                Case ColumnTypes.Double
                    col = New DataColumn(columnName, GetType(System.Double))

                Case ColumnTypes.DateTime
                    col = New DataColumn(columnName, GetType(System.DateTime))

                Case ColumnTypes.Binary
                    col = New DataColumn(columnName, GetType(System.Byte()))

                Case ColumnTypes.GUID
                    col = New DataColumn(columnName, GetType(System.Guid))

                Case ColumnTypes.Text
                    col = New DataColumn(columnName, GetType(System.String))
                    ' ANSI/Unicode Code page
                    ret = JetRetrieveColumn(db.SessionID, columnList.tableid, columnList.columnidCp, iPtr, 4, size, 0, ret_info)
                    If ret <> 0 Then
                        Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                    End If
                    col.ExtendedProperties.Add("CodePage", Marshal.ReadInt32(iPtr))

                Case ColumnTypes.LongText
                    col = New DataColumn(columnName, GetType(System.String))
                    ' Code page
                    ret = JetRetrieveColumn(db.SessionID, columnList.tableid, columnList.columnidCp, iPtr, 4, size, 0, ret_info)
                    If ret <> 0 Then
                        Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                    End If
                    col.ExtendedProperties.Add("CodePage", Marshal.ReadInt32(iPtr))

                Case ColumnTypes.UInt32
                    col = New DataColumn(columnName, GetType(System.UInt32))

                Case ColumnTypes.Long
                    col = New DataColumn(columnName, GetType(System.Int64))

                Case ColumnTypes.UInt16
                    col = New DataColumn(columnName, GetType(System.UInt16))

                Case Else
                    Throw New ApplicationException("Unsupported column type " & columnType)

            End Select

            ' store the Column ID in the column for update purposes
            col.ExtendedProperties.Add("ColumnID", columnID)
            dt.Columns.Add(col)

            ret = JetMove(db.SessionID, columnList.tableid, JET_MoveNext, 0)

        Loop

        Marshal.FreeHGlobal(iPtr)
        JetCloseTable(db.SessionID, columnList.tableid)

        iPtr = Marshal.AllocHGlobal(BUF_SIZE)

        ' Add data rows
        ret = JetMove(db.SessionID, tableID, JET_MoveFirst, 0)
        Do While ret = 0
            dr = dt.NewRow

            For Each col In dt.Columns
                Select Case True
                    Case col.DataType.Equals(GetType(Boolean))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 1, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = CBool(Marshal.ReadByte(iPtr))
                        Else
                            dr(col.ColumnName) = False
                        End If

                    Case col.DataType.Equals(GetType(Byte))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 1, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = Marshal.ReadByte(iPtr)
                        End If

                    Case col.DataType.Equals(GetType(Short)) Or col.DataType.Equals(GetType(UInt16))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 2, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = Marshal.ReadInt16(iPtr)
                        End If

                    Case col.DataType.Equals(GetType(Integer)) Or col.DataType.Equals(GetType(UInt32))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 4, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = Marshal.ReadInt32(iPtr)
                        End If

                    Case col.DataType.Equals(GetType(Int64))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 8, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = Marshal.ReadInt64(iPtr)
                        End If

                    Case col.DataType.Equals(GetType(Decimal))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 8, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = CType(Marshal.PtrToStructure(iPtr, GetType(Decimal)), Decimal)
                        End If

                    Case col.DataType.Equals(GetType(Single))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 4, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = CType(Marshal.PtrToStructure(iPtr, GetType(Single)), Single)
                        End If

                    Case col.DataType.Equals(GetType(Double))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 8, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            dr(col.ColumnName) = CType(Marshal.PtrToStructure(iPtr, GetType(Double)), Double)
                        End If

                    Case col.DataType.Equals(GetType(DateTime))
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), iPtr, 8, size, 0, ret_info)
                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            Dim d As Double
                            d = CType(Marshal.PtrToStructure(iPtr, GetType(Double)), Double)
                            dr(col.ColumnName) = Date.FromOADate(d).ToLocalTime
                        End If

                    Case col.DataType.Equals(GetType(Byte()))
                        Dim p As IntPtr
                        p = iPtr
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), p, BUF_SIZE, size, 0, ret_info)

                        If ret = JET_wrnBufferTruncated Then
                            ' resize buffer and re-read
                            p = Marshal.AllocHGlobal(size)
                            ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), p, size, size, 0, ret_info)

                        End If

                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            If p <> iPtr Then
                                Marshal.FreeHGlobal(p)
                            End If
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            ' copy to a byte array
                            Dim buf(size - 1) As Byte
                            Marshal.Copy(p, buf, 0, size)
                            dr(col.ColumnName) = buf
                        End If

                        If p <> iPtr Then
                            Marshal.FreeHGlobal(p)
                        End If

                    Case col.DataType.Equals(GetType(Guid))
                        Dim p As IntPtr
                        p = iPtr
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), p, BUF_SIZE, size, 0, ret_info)

                        If ret = JET_wrnBufferTruncated Then
                            ' resize and re-read
                            p = Marshal.AllocHGlobal(size)
                            ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), p, size, size, 0, ret_info)
                        End If

                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            If p <> iPtr Then
                                Marshal.FreeHGlobal(p)
                            End If

                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull Then
                            ' copy to a byte array
                            Dim buf(size - 1) As Byte
                            Marshal.Copy(p, buf, 0, size)
                            Dim g As Guid = New Guid(buf)

                            dr(col.ColumnName) = g

                        End If

                        If p <> iPtr Then
                            Marshal.FreeHGlobal(p)
                        End If

                    Case col.DataType.Equals(GetType(String))
                        Dim p As IntPtr
                        p = iPtr
                        ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), p, BUF_SIZE, size, 0, ret_info)
                        If ret = JET_wrnBufferTruncated Then
                            ' resize and re-read
                            p = Marshal.AllocHGlobal(size)
                            ret = JetRetrieveColumn(db.SessionID, tableID, CInt(col.ExtendedProperties("ColumnID")), p, size, size, 0, ret_info)
                        End If

                        If ret <> 0 And ret <> JET_wrnColumnNull Then
                            If p <> iPtr Then
                                Marshal.FreeHGlobal(p)
                            End If
                            Throw New ApplicationException("JetRetrieveColumn: " & ParseError(ret))
                        End If

                        If ret <> JET_wrnColumnNull AndAlso size > 0 Then
                            If CInt(col.ExtendedProperties("CodePage")) = 1200 Then
                                dr(col.ColumnName) = Marshal.PtrToStringUni(iPtr, CInt((size / 2) - 1))
                            Else
                                dr(col.ColumnName) = Marshal.PtrToStringAnsi(iPtr, size)
                            End If

                        End If

                        If p <> iPtr Then
                            Marshal.FreeHGlobal(p)
                        End If

                    Case Else
                        Throw New ApplicationException("Unsupported Data type")

                End Select

            Next

            dt.Rows.Add(dr)
            ret = JetMove(db.SessionID, tableID, JET_MoveNext, 0)

        Loop

        ' close the table
        ret = JetCloseTable(db.SessionID, tableID)
        If ret <> 0 Then
            Throw New ApplicationException("JetCloseTable: " & ParseError(ret))
        End If

        CloseDatabase(db)

        Return dt

    End Function

    Shared Sub UpdateRow(dr As DataRow)
        Dim db As ESEDatabase
        Dim table_id As IntPtr
        Dim ret As Integer
        Dim req_size As Integer
        Dim dataBytes() As Byte

        db = OpenDatabase(CStr(dr.Table.ExtendedProperties("DatabaseFile")))

        ' open the table
        ret = JetOpenTable(db.SessionID, db.DatabaseID, dr.Table.TableName, 0, 0, 8, table_id)
        If ret <> 0 Then
            Throw New ApplicationException("JetOpenTable: " & ParseError(ret))
        End If

        ' move to row
        ret = JetMove(db.SessionID, table_id, JET_MoveFirst, 0)
        ret = JetMove(db.SessionID, table_id, dr.Table.Rows.IndexOf(dr), 0)
        If ret <> 0 Then
            Throw New ApplicationException("JetMove: " & ParseError(ret))
        End If

        ' BeginTransaction
        ret = JetBeginTransaction(db.SessionID)
        If ret <> 0 Then
            Throw New ApplicationException("JetBeginTransaction: " & ParseError(ret))
        End If

        ' PrepareUpdate
        ret = JetPrepareUpdate(db.SessionID, table_id, JET_PrepReplace)
        If ret <> 0 Then
            Throw New ApplicationException("JetPrepareUpdate: " & ParseError(ret))
        End If

        ' SetColumn
        For Each col As DataColumn In dr.Table.Columns
            If IsDBNull(dr(col.ColumnName)) OrElse String.IsNullOrEmpty(dr(col.ColumnName).ToString) Then
                JetSetColumn(db.SessionID, table_id, CInt(col.ExtendedProperties("ColumnID")), Nothing, 0, &H20, Nothing)
            Else
                Select Case True
                    Case col.DataType.Equals(GetType(System.String))
                        Dim text As String = CStr(dr(col.ColumnName))
                        dataBytes = System.Text.UnicodeEncoding.Unicode.GetBytes(text)
                        JetSetColumn(db.SessionID, table_id, CInt(col.ExtendedProperties("ColumnID")), dataBytes, dataBytes.Length + 2, 0, Nothing)

                    Case col.DataType.Equals(GetType(System.Int32))
                        Dim value As Integer = CInt(dr(col.ColumnName))
                        dataBytes = BitConverter.GetBytes(value)
                        JetSetColumn(db.SessionID, table_id, CInt(col.ExtendedProperties("ColumnID")), dataBytes, dataBytes.Length, 0, Nothing)

                    Case col.DataType.Equals(GetType(System.Guid))
                        Dim text As Guid = New Guid(dr(col.ColumnName).ToString)
                        dataBytes = text.ToByteArray
                        JetSetColumn(db.SessionID, table_id, CInt(col.ExtendedProperties("ColumnID")), dataBytes, dataBytes.Length, 0, Nothing)

                End Select

            End If

        Next

        ' JetUpdate
        ret = JetUpdate(db.SessionID, table_id, Nothing, 0, req_size)
        If ret <> 0 Then
            Throw New ApplicationException("JetUpdate: " & ParseError(ret))
        End If

        ' CommitTransaction
        ret = JetCommitTransaction(db.SessionID, 0)
        If ret <> 0 Then
            Throw New ApplicationException("JetCommitTransaction: " & ParseError(ret))
        End If

        ' close the table
        ret = JetCloseTable(db.SessionID, table_id)
        If ret <> 0 Then
            Throw New ApplicationException("JetCloseTable: " & ParseError(ret))
        End If

        CloseDatabase(db)

    End Sub

    Private Shared Function OpenDatabase(path As String) As ESEDatabase
        Dim db As New ESEDatabase
        Dim ret As Integer
        Dim pageSize As Integer
        Dim instanceID As IntPtr
        Dim sessionID As IntPtr
        Dim databaseID As Integer

        Try
            db.Path = path

            ' Get the size of the Database Page
            ret = JetGetDatabaseFileInfo(db.Path, pageSize, 4, JET_DbInfoPageSize)
            Select Case ret
                Case 0
                    ' success

                Case -1032
                    Throw New IO.IOException("The Groove database is in use.  Close Groove first and try again.  If Groove is suspended (not visible in taskbar), you can use the force close menu item.")

                Case Else
                    Throw New ApplicationException("JetGetDatabaseFileInfo: " & ParseError(ret))

            End Select

            ' set the page size
            ret = JetSetSystemParameter(IntPtr.Zero, IntPtr.Zero, JET_paramDatabasePageSize, pageSize, Nothing)
            If ret <> 0 Then
                Throw New ApplicationException("JetSetSystemParameter: " & ParseError(ret))
            End If

            ' Check state of database
            If Not IsDatabaseClean(db.Path) Then
                RecoverDb(db.Path)
            End If

            ' create an instance
            ret = JetCreateInstance(instanceID, "GrooveAccess")
            If ret <> 0 Then
                Throw New ApplicationException("JetCreateInstance: " & ParseError(ret))
            End If

            db.InstanceID = instanceID

            ' set Log directories (.log and .jrs files)
            ret = JetSetSystemParameter(db.InstanceID, IntPtr.Zero, JET_paramLogFilePath, Nothing, System.IO.Path.GetDirectoryName(db.Path))
            If ret <> 0 Then
                Throw New ApplicationException("JetSetSystemParameter: " & ParseError(ret))
            End If

            ' Set system path (.chk file)
            ret = JetSetSystemParameter(db.InstanceID, IntPtr.Zero, JET_paramSystemPath, Nothing, System.IO.Path.GetDirectoryName(db.Path))
            If ret <> 0 Then
                Throw New ApplicationException("JetSetSystemParameter: " & ParseError(ret))
            End If

            ' Set circular logging
            ret = JetSetSystemParameter(db.InstanceID, IntPtr.Zero, JET_paramCircularLogging, 1, Nothing)
            If ret <> 0 Then
                Throw New ApplicationException("JetSetSystemParameter: " & ParseError(ret))
            End If

            ' initialize the instance
            ret = JetInit(db.InstanceID)
            If ret <> 0 Then
                Throw New ApplicationException("JetInit: " & ParseError(ret))
            End If

            ' start the session
            ret = JetBeginSession(db.InstanceID, sessionID, Nothing, Nothing)
            If ret <> 0 Then
                Throw New ApplicationException("JetBeginSession: " & ParseError(ret))
            End If

            db.SessionID = sessionID

            ' attach the database file
            ret = JetAttachDatabase(db.SessionID, db.Path, 0)
            If ret <> 0 Then
                Throw New ApplicationException("JetAttachDatabase: " & ParseError(ret))
            End If

            ' open the database
            ret = JetOpenDatabase(db.SessionID, db.Path, Nothing, databaseID, 0)
            If ret <> 0 Then
                Throw New ApplicationException("JetOpenDatabase: " & ParseError(ret))
            End If

            db.DatabaseID = databaseID

            Return db

        Catch ex As Exception
            ' clean up if possible
            CloseDatabase(db)
            Throw

        End Try

    End Function

    Private Shared Sub CloseDatabase(db As ESEDatabase)
        Dim ret As Integer

        If db.DatabaseID <> 0 Then
            ' close the database
            ret = JetCloseDatabase(db.SessionID, db.DatabaseID, DatabaseFlags.OpenDefault)
            If ret <> 0 Then
                Throw New ApplicationException("JetCloseDatabase: " & ParseError(ret))
            End If

            ' detach the database file
            ret = JetDetachDatabase(db.SessionID, db.Path)
            If ret <> 0 Then
                Throw New ApplicationException("JetDetachDatabase: " & ParseError(ret))
            End If

            ' end the session
            ret = JetEndSession(db.SessionID, 0)
            If ret <> 0 Then
                Throw New ApplicationException("JetEndSession: " & ParseError(ret))
            End If

        End If

        If db.InstanceID <> IntPtr.Zero Then
            ' terminate the instance
            ret = JetTerm(db.InstanceID)
            If ret <> 0 Then
                Throw New ApplicationException("JetTerm: " & ParseError(ret))
            End If

        End If

    End Sub

    Private Shared Function ParseError(ByVal code As Integer) As String
        Dim msg As String
        Dim ret As Integer

        msg = Space(256)
        ret = JetGetSystemParameter(IntPtr.Zero, IntPtr.Zero, JET_paramErrorToString, code, msg, 256)
        If ret <> 0 Then
            Throw New ApplicationException("Can't get error message for code " & code)
        End If

        Return msg

    End Function

    Private Shared Function IsDatabaseClean(path As String) As Boolean
        Dim ret As Integer
        Dim info As New JET_DBINFOMISC

        ret = JetGetDatabaseFileInfo(path, info, Marshal.SizeOf(GetType(JET_DBINFOMISC)), 14) ' Misc info
        If ret <> 0 Then
            Throw New ApplicationException("JetGetDatabaseFileInfo: " & ParseError(ret))
        End If

        Return info.dbstate = 3 ' 3=Clean, 2=Dirty

    End Function

    Private Shared Sub RecoverDb(path As String)
        Dim t As Process
        Dim pi As ProcessStartInfo

        pi = New ProcessStartInfo
        With pi
            .FileName = "esentutl.exe"
            .Arguments = "/r edb"
            .WindowStyle = ProcessWindowStyle.Hidden
            .WorkingDirectory = IO.Path.GetDirectoryName(path)
        End With

        t = New Process()
        With t
            .StartInfo = pi
            .Start()
            .WaitForExit()
            .Dispose()
        End With

    End Sub

End Class
